[% PROCESS helpheader.html %]

<style type="text/css">
<!--
h1 {
	font-size:2em;
}

h3 {
	font-size:large;
}
p {
	font-size:1.2em;
}
li {
	font-size:1.2em;
	padding:0em 0em 0em 1em;
/*	text-indent: -1em; */
}

DIV.bigcode {
	width: 900px; 
	padding:1em;
	overflow-x: auto; 
	background-color: #ffffc6;
	font-family: courier, "courier new", monospace;
	font-size:1em;
}

DIV.smallcode {
	width: 900px; 
	padding: 1em;
	margin-left:2.8em;
	overflow-x: auto; 
	background-color: #ffffc6;
	font-family: courier, "courier new", monospace;
	font-size:1em;
}
-->
</style>

<script>
function PSHreadOnlyCB() {
   return false;
}
</script>



<div style="width:80%; margin-left:10%;">
<p>&nbsp;</p>
<a href="http://code.google.com/p/srvrpowerctrl/" target="_blank"><img src="images/icon.png" alt="PostScan" width="114" height="114" hspace="10" align="right" /></a>
<h3>PostScan plugin help</h3>
<p>PostScan's job is to run special queries or SQL (Structured Query Language) commands after Squeezebox Server has scanned your music library.
Why might you want to do that?  Potentially, for many reasons.
One reason (and the reason why I developed the plugin for my own use) is to clean up the display of tags that Squeezebox Server scans from your music library.
<br>
<p style="vertical-align:text-top;">For example:</p>

<textarea style="border-style: inset; border-width: 2px; font-size:small" id="example1" rows="3" cols="50" wrap="off" name="example1" readonly="readonly">
UPDATE genres
set namesort = 'ZZZZZZZZ NO GENRE'
where name = 'No Genre';
</textarea>
<p>In the above example the 'namesort' entry of the 'No Genre' genre is changed so that 'No Genre' appears at the very bottom of the list of genres as displayed by Squeezebox Server.

<br><br>Another example: perhaps 'No Genre' is too prosaic for your tastes..</p>
<textarea style="border-style: inset; border-width: 2px; font-size:small" id="example1" rows="3" cols="50" wrap="off" name="example1" readonly="readonly">
UPDATE genres
set name = 'Senza indicazione di genere'
where name = 'No Genre';
</textarea>
<p>Now, the 'No Genre' genre will be changed to something much more <i>chic and Italianate.</i>

<br><br>In order to successfully craft such queries, you'll need to have some understanding of the <a href="http://www.w3schools.com/sql/default.asp" target="_blank">SQL language</a> and of <a href="http://wiki.slimdevices.com/index.php/SlimServerDatabaseStructure" target="_blank">Squeezebox Server's database structure.</a>

<br><br>There are many, many clever things you can do with PostScan queries.  You can start by forcing Squeezebox Server to display <u>your music</u> (Genres, Artists, Albums, Titles, etc.) in the order <u>you</u> wish to see them.

<br><br>Final Example: brute force reordering of genres..<br>
<textarea style="border-style: inset; border-width: 2px; font-size:small" id="example1" rows="4" cols="70" wrap="off" name="example1" readonly="readonly">
UPDATE genres
set namesort = CASE 
when name = 'Rock' then 'A ROCK'
when name = 'Jazz' then 'B JAZZ'
when name = 'Easy Listening' then 'ZZZZZZZZZZZZZZZZZZZ EASY LISTENING'
Else namesort
End ;
</textarea>
<br>That sort of CASE query can be used to change the display order of genres (or artists or albums, etc.) to the precise order in which you wish to see them.

</p>


<hr /><h3>Settings Instructions</h3>

<p>Near the top of the PostScan settings page, you'll see:</p>
<div class="bigcode">
	[% WRAPPER setting title="PLUGIN_POSTSCAN_GLOBALENABLED" desc="PLUGIN_POSTSCAN_GLOBALENABLED_DISC" %] &nbsp;
		<input id="globalenabled" name="globalenabled" type="checkbox" checked onClick="return PSHreadOnlyCB ()" >
	[% END %]
</div>
<p>This allows you to globally enable/disable PostScan processing.  By un-checking the box, PostScan is disabled and none of your queries will run 'PostScan'.

<p>You can enter your custom queries into the forms:</p>

<div class="bigcode">
	[% WRAPPER setting title="PLUGIN_POSTSCAN_QUERIES" desc="PLUGIN_POSTSCAN_QUERIES_LONGDESC" %]
		SQLite SQL Query/Command Statement(s).. 
	[% END %]
		<table cellspacing=5px>
			<tr>
				<td style="padding-top:2px;">
					<input id="name1" type="text" class="stdedit" name="name1" value="Custom Query #1" size="37">
				</td>
				<td>
					<textarea style="border-style: inset; border-width: 2px; font-size:small" id="query1" rows="10" cols="50" wrap="off" name="query1" readonly="readonly">[% "PLUGIN_POSTSCAN_QUERY" | string %]</textarea>
				</td>
				<td style="padding-top:4px;">
					<input name="testquery1" type="submit" class="stdclick" value=[% "PLUGIN_POSTSCAN_TESTBUTTON" | string %] id="test1">
					&nbsp;<input name="validated1" type="checkbox" onClick="return PSHreadOnlyCB ()" >&nbsp;[% "PLUGIN_POSTSCAN_QUERY_VALIDATED" | string %]
					<br><br>[% "PLUGIN_POSTSCAN_QUERY_SCANTYPE" | string %]<br>
					<select name="scantype1" id="scantype1">
						<option value=0 selected>[% "PLUGIN_POSTSCAN_SCANTYPE_DISABLED" | string %]</option>
						<option value=1 >[% "PLUGIN_POSTSCAN_SCANTYPE_WIPECACHE" | string %]</option>
						<option value=2 >[% "PLUGIN_POSTSCAN_SCANTYPE_RESCAN" | string %]</option>
						<option value=4 >[% "PLUGIN_POSTSCAN_SCANTYPE_PLAYLISTS" | string %]</option>
						<option value=8 >[% "PLUGIN_POSTSCAN_SCANTYPE_ALL" | string %]</option>
					</select>
				</td>
			</tr>
		</table>
</div>
<p>Enter the a name by which you want to refer to your query in the small edit box and enter your SQL query in the large text box.  You'll press the 'Test' button to try out the query.  If it succeeds (i.e. if the SQL syntax is correct), the 'validated' box will be checked.  Once your query is validated, you can select an entry from the drop-down list to specify the type of scan after which the query should be run.

<br><br>You can enter multiple SQL statements in each query text box.  Just remember to end each complete statement with a semicolon (&nbsp;;&nbsp;).

<br><br>To delete a PostScan query, simply delete the text in the name edit box and the query statements in the large text box and press 'Apply' at the bottom of the page.
</p>


<hr /><h3>Special Functions for SQLite</h3>
<p>PostScan defines several 'helper' functions normally not available with SQLite.  These functions are:

<br><br><b>SUBSTRING(EXPR,OFFSET,LENGTH,REPLACEMENT)</b><br><br>
SQLite supports the SUBSTR function.  SUBSTRING is offered for compatibility with MySQL query syntax and to provide an extra bit of functionality.  <a href="http://perldoc.perl.org/functions/substr.html" target="_blank">SUBSTRING() behaves just like the Perl substring() function.</a>

<br><br><b>INSTR(str,substr,position)</b><br><br>
SQLite normally has no INSTR or LOCATE function to return the offset of a string within a string.  PostScan's INSTR() function <a href="http://perldoc.perl.org/functions/index.html" target="_blank">behaves the same way as the Perl index() function.</a>

<br><br>Example: given a genre named 'Jazz & Blues'..<br>
<textarea style="border-style: inset; border-width: 2px; font-size:small" id="example1" rows="4" cols="70" wrap="off" name="example1" readonly="readonly">
UPDATE genres
set name = substring(name, 0, instr(name, '&')) || 'and R&B'
where name = 'Jazz & Blues';
</textarea>
<br>That should transform the 'Jazz & Blues' genre into 'Jazz and R&B'.



<br><br><b>PSREPLACE(STR,REGEX)</b><br><br>
SQLite supports the REGEXP function, but that can be used only to SELECT data, not to modify it.

PostScan's PSREPLACE function allows an UPDATE query to change data in a column or field using regular expressions.

PSREPLACE uses a <a href="http://perldoc.perl.org/perlre.html#Regular-Expressions" target="_blank">sub-set of Perl's regular expression syntax</a>, in that it only supports regular expressions in the form of:


<br><br>'s/search/replace/g'

<br><br>..where the 'g' (for global replacing) is optional.

<br><br>Example: given any genre that contain 'English' in the name..<br>
<textarea style="border-style: inset; border-width: 2px; font-size:small" id="example1" rows="4" cols="70" wrap="off" name="example1" readonly="readonly">
UPDATE genres
set name = psreplace(name, 's/English/Blighty/g');
</textarea>
<br>So..that should change, for instance, '60s English Rock' into '60s Blighty Rock'.

<br><br>Another Example: given any genre that begins with a prefix of characters followed by an underscore..<br>
<textarea style="border-style: inset; border-width: 2px; font-size:small" id="example1" rows="4" cols="70" wrap="off" name="example1" readonly="readonly">
UPDATE genres
set name = psreplace(name, 's/^.*?_(.*)$/$1/')
</textarea>
<br>So..that should change, for instance, 'a_Medieval' into 'Medieval'.

<br><br><b>PSMATCH(STR,REGEX)</b><br><br>
PSMATCH is like the REGEXP function, except that gives you a little more regular expression matching horsepower.  If STR is matched by REGEX, PSMATCH eturns STR.  If there is no match, PSMATCH returns an empty string.

<br><br>Example:<br>
<textarea style="border-style: inset; border-width: 2px; font-size:small" id="example1" rows="4" cols="70" wrap="off" name="example1" readonly="readonly">
UPDATE genres
SET namesort = psreplace(namesort, 's/^(.*)$/"ZZZZZ $1"/')
WHERE namesort != psmatch(namesort, 'm/^([[:alpha:]])\1* .*$/');
</textarea>
<br>That query would move all genres whose namesort is NOT prefixed with single or repeated letters followed by a space to the bottom of the genre list.

</p>


<hr /><h3>Trouble-shooting PostScan Queries</h3>
<p>Often, it's best to develop and trouble-shoot a query outside of Squeezebox Server's web interface and the PostScan settings page.  The 'outside development' tool that you use depends on the database engine your Squeezebox Server is using.

Look at the <a href="/settings/server/status.html?player=" target="_blank;">Settings&rarr;Information page</a> to see which DB engine is in use.  Look for the 'Database Version' information.

<br><br>If you're using SQLite as SBS's database engine, you may want to try <a href="https://addons.mozilla.org/en-US/firefox/addon/5817" target="_blank">SQLite Manager</a> which exists as a Firefox plugin.  Be aware that, because of file locking considerations, you may have to stop Squeezebox Server in order to open it's database in SQLite Manager.  Another option is to copy the Squeezebox Server 'squeezebox.db' database and work with the copy.
Or, if you prefer, you can turn SBS's exclusive db locking off by executing the following (linux) CLI command:</p>

<div class="bigcode"># echo 'pragma locking_mode = NORMAL' | nc -w 2 localhost 9090</div>


<p>Alternatly, if you are using MySQL as Squeezebox Server's database engine, you can use the Query Browser in the <a href="http://dev.mysql.com/downloads/" target="_blank;">MySQL Workbench GUI Tool</a>.

In order to access SBS's MySQL database, you'll need to jump through a couple of hoops:</p>

<ul>
<li>Find the 'my.tt' file amongst the Squeezebox Server program files, open it in a text editor and comment out the 'bind-address' line by placing a '#' at the beginning of the line.  Save the file and restart Squeezebox Server.
<li>Using the MySQL Workbench, connect to SBS's database on port 9092, rather than on the default port of 3306.
</ul> 


<hr /><h3>More Help</h3>
<p>Your best source for further help with PostScan is on the <a href="http://forums.slimdevices.com/showthread.php?t=79778&page=999" target="_blank">SqueezeBox 3rd Party Plugins</a> forum.  This is also where you may report any bugs that you find.</p>

<hr /><h3>Acknowledgments</h3>

<p>The following individuals provided helpful hints during PostScan's development:<br>
<br>Andy Grundman
<br>Michael Herger
<br>Erland Isaksson
<br>Philip Meyer
<br>JJZolx
</p>

<p>&nbsp;</p>

</div>

[% PROCESS helpfooter.html %]
